<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;

class StatisticPhoneRecord extends Model
{
    use SoftDeletes;

    protected $table = 'statistic_phone_records';

    protected $fillable = ['call_times', 'visit_number', 'connect_number', 'login_number', 'charge_number', 'member_number', 'type', 'any_id'];

    //统计
    public function count()
    {
        //获取昨天0点
        $yesterday_zero = Carbon::yesterday();

        //获取今天0点
        $today_zero = Carbon::yesterday()->addHour(24);

        //单人统计
        $single_static = $this->countSingleUserPhone($yesterday_zero, $today_zero);

        //整体统计
        $all_static = $this->countAllUserPhone($yesterday_zero, $today_zero);

        //分支统计
        $branch_static = $this->countSectionAndBranchPhone($yesterday_zero, $today_zero, 3);

        //部门统计
        $section_static = $this->countSectionAndBranchPhone($yesterday_zero, $today_zero, 2);

        if (count($single_static)) {
            $this->createStaticPhoneRecord($single_static, 0);
        }

        if (count($all_static)) {
            $this->createStaticPhoneRecord($all_static, 1);
        }

        if (count($section_static)) {
            $this->createStaticPhoneRecord($section_static, 2);
        }

        if (count($branch_static)) {
            $this->createStaticPhoneRecord($branch_static, 3);
        }

        return true;
    }

    public function createStaticPhoneRecord($statics, $type)
    {
        //0、单个客服 1、整体客服 2、部门客服 3、分支客服
        if ($type == 1) {
            self::create($statics);
        } else {
            DB::table($this->table)->insert($statics);
        }

        return true;
    }

    //单个客服
    public function countSingleUserPhone($yesterday_zero, $today_zero)
    {
        DB::statement("set @@sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'");//取消严格模式，严格模式不能使用groupBy

        $distinct_users = DB::table('phone_records')
            ->whereBetween('created_at', [$yesterday_zero, $today_zero])
            ->groupBy('user_id')
            ->get(['user_id']);

        if (count($distinct_users) == 0) {
            return [];
        }

        $now = Carbon::now()->toDateTimeString();
        $distinct_users_arr = [];
        foreach ($distinct_users->toArray() as $key => $user) {
            //统计昨天单人一整天的回访次数
            $call_times = DB::table('phone_records')
                ->where('user_id', $user->user_id)
                ->whereBetween('created_at', [$yesterday_zero, $today_zero])
                ->count();

            //获取昨天单人一整天的回访集合
            $visit_obj = DB::table('phone_records')
                ->where('user_id', $user->user_id)
                ->whereBetween('created_at', [$yesterday_zero, $today_zero])
                ->groupBy('fs_user_id')
                ->get(['fs_user_id']);

            //统计昨天单人一整天的回访人数
            $visit_number = count($visit_obj);

            //单人回访接通的集合
            $single_record = DB::table('phone_records')
                ->select('id', 'call_status', 'fs_user_id', DB::raw('group_concat(created_at) as time'))
                ->whereBetween('created_at', [$yesterday_zero, $today_zero])
                ->where('user_id', $user->user_id)
                ->where('call_status', '=', 'completed')
                ->groupBy('fs_user_id')
                ->get();

            //获取昨天单人回访接通的人数
            $single_connect_number = count($single_record);

            //统计单人回访后登录的人数
            $single_number = $this->countLoginAndChargeNumber($single_record);

            //统计昨天单人总任务分配的会员数
            $member_number_record = DB::table('tasks')
                ->select('carry_user_id', DB::raw('sum(count) as member_number'))
                ->where('carry_user_id', $user->user_id)
                ->whereBetween('created_at', [$yesterday_zero, $today_zero])
                ->groupBy('carry_user_id')
                ->get();

            if (count($member_number_record)) {
                $member_number = $member_number_record[0]->member_number;
            } else {
                $member_number = 0;
            }

            $distinct_users_arr[$key]['call_times'] = $call_times;
            $distinct_users_arr[$key]['visit_number'] = $visit_number;
            $distinct_users_arr[$key]['connect_number'] = $single_connect_number;
            $distinct_users_arr[$key]['login_number'] = $single_number['login_number'];
            $distinct_users_arr[$key]['charge_number'] = $single_number['charge_number'];
            $distinct_users_arr[$key]['member_number'] = $member_number;
            $distinct_users_arr[$key]['type'] = 0;
            $distinct_users_arr[$key]['any_id'] = $user->user_id;
            $distinct_users_arr[$key]['created_at'] = $now;
            $distinct_users_arr[$key]['updated_at'] = $now;
        }

        return $distinct_users_arr;
    }

    //整体客服
    public function countAllUserPhone($yesterday_zero, $today_zero)
    {
        DB::statement("set @@sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'");//取消严格模式，严格模式不能使用groupBy

        //统计昨天整体一整天的回访次数
        $call_times = DB::table('phone_records')
            ->whereBetween('created_at', [$yesterday_zero, $today_zero])
            ->count();

        //获取昨天整体一整天的回访记录
        $visit_record = DB::table('phone_records')
            ->whereBetween('created_at', [$yesterday_zero, $today_zero])
            ->groupBy('fs_user_id')
            ->get();

        //统计回访人数
        $visit_number = count($visit_record);

        //整体回访接通的集合
        $all_record = DB::table('phone_records')
            ->select('id', 'call_status', 'fs_user_id', DB::raw('group_concat(created_at) as time'))
            ->whereBetween('created_at', [$yesterday_zero, $today_zero])
            ->where('call_status', '=', 'completed')
            ->groupBy('fs_user_id')
            ->get();

        //统计接通人数
        $all_connect_number = count($all_record);

        //统计整体回访后登录的人数和充值人数
        $all_number = $this->countLoginAndChargeNumber($all_record);

        $all_static = [];
        $all_static['call_times'] = $call_times;
        $all_static['visit_number'] = $visit_number;
        $all_static['connect_number'] = $all_connect_number;
        $all_static['login_number'] = $all_number['login_number'];
        $all_static['charge_number'] = $all_number['charge_number'];
        $all_static['type'] = 1;

        return $all_static;
    }

    //部门和分支客服客服
    public function countSectionAndBranchPhone($yesterday_zero, $today_zero, $type)
    {
        DB::statement("set @@sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'");//取消严格模式，严格模式不能使用groupBy
        if ($type == 3) {
            //统计昨天各个分支一整天的回访次数
            $call_times = $this->getSectionAndBranchCount($yesterday_zero, $today_zero, 'count(users.branch_id) as call_times, users.branch_id', 'users.branch_id');

            //统计昨天各个分支一整天的回访人数
            $visit_number = $this->getSectionAndBranchCount($yesterday_zero, $today_zero, 'count(distinct(records.fs_user_id)) as visit_number, users.branch_id', 'users.branch_id');

            //获取各个分支回访接通的集合
            $records = $this->getSectionAndBranchCount($yesterday_zero, $today_zero, 'group_concat(distinct(records.fs_user_id) ORDER BY records.created_at ASC) as idAndTime, users.branch_id', 'users.branch_id', 'completed');
        }
        if ($type == 2) {
            //统计昨天各个部门一整天的回访次数
            $call_times = $this->getSectionAndBranchCount($yesterday_zero, $today_zero, 'count(users.section_id) as call_times, users.section_id', 'users.section_id');

            //统计昨天各个部门一整天的回访人数
            $visit_number = $this->getSectionAndBranchCount($yesterday_zero, $today_zero, 'count(distinct(records.fs_user_id)) as visit_number, users.section_id', 'users.section_id');

            //获取各个部门回访接通的集合
            $records = $this->getSectionAndBranchCount($yesterday_zero, $today_zero, 'group_concat(distinct(records.fs_user_id) ORDER BY records.created_at ASC) as idAndTime, users.section_id', 'users.section_id', 'completed');
        }

        if (count($call_times) == 0) {
            return [];
        }

        $value = [];

        //切割合并接通的id
        if (count($records)) {
            foreach ($records->toArray() as $record) {
                $record->ids = explode(',', $record->idAndTime);
                $value = array_merge($value, array_values($record->ids));
            }
        }

        //得到接通集合的ids 去重
        $ids_diff = array_unique($value);

        $record_times = [];

        //得到每个最早接通的时间
        $sql = "(select call_status,fs_user_id, min(created_at) as created_at from phone_records where created_at between '" . $yesterday_zero . "' and '" . $today_zero . "' group by phone_records.fs_user_id) as phone_records";

        if ($ids_diff) {
            $record_times = DB::table(DB::raw($sql))
                ->whereIn('fs_user_id', $ids_diff)
                ->where('call_status', '=', 'completed')
                ->groupBy('fs_user_id')
                ->get()->toArray();
        }

        if ($type == 3) {
            //获取昨天各个分支回访接通的人数
            foreach ($call_times as $times) {
                if (count($visit_number)) {
                    foreach ($visit_number as $number) {
                        if ($times->branch_id == $number->branch_id) {
                            $times->visit_number = $number->visit_number;
                        }
                    }
                }
                if (count($records)) {
                    foreach ($records as $brecord) {
                        if ($times->branch_id == $brecord->branch_id) {
                            $times->branch_connect_number = count($brecord->ids);
                            $times->ids = $brecord->ids;
                        }
                    }
                } else {
                    $times->branch_connect_number = 0;
                    $times->ids = [];
                }
            }
        }

        if ($type == 2) {
            //获取昨天各个部门回访接通的人数
            foreach ($call_times as $times) {
                if (count($visit_number)) {
                    foreach ($visit_number as $number) {
                        if ($times->section_id == $number->section_id) {
                            $times->visit_number = $number->visit_number;
                        }
                    }
                }
                if (count($records)) {
                    foreach ($records as $srecord) {
                        if ($times->section_id == $srecord->section_id) {
                            $times->section_connect_number = count($srecord->ids);
                            $times->ids = $srecord->ids;
                        }
                    }
                } else {
                    $times->section_connect_number = 0;
                    $times->ids = [];
                }
            }
        }

        //统计部门和分支回访数据
        $count_data = $this->countData($counts = $call_times, $record_times, $ids_diff, $type);

        return $count_data;
    }

    public function getSectionAndBranchCount($yesterday_zero, $today_zero, $sql, $column, $call_status = '')
    {
        if ($call_status == 'completed') {
            return DB::table('users')
                ->select(DB::raw($sql))
                ->leftJoin('phone_records as records', 'users.id', '=', 'records.user_id')
                ->whereBetween('records.created_at', [$yesterday_zero, $today_zero])
                ->where('call_status', '=', $call_status)
                ->whereNotNull($column)
                ->groupBy($column)
                ->get();
        } else {
            return DB::table('users')
                ->select(DB::raw($sql))
                ->leftJoin('phone_records as records', 'users.id', '=', 'records.user_id')
                ->whereBetween('records.created_at', [$yesterday_zero, $today_zero])
                ->whereNotNull($column)
                ->groupBy($column)
                ->get();
        }
    }

    public function countLoginAndChargeNumber($record)
    {
        $login_number = 0;
        $charge_number = 0;
        $number = [];
        //统计回访后登录的人数和充值的人数
        if ($record) {
            $record_arr = $record->toArray();
            $ids = array_column($record_arr, 'fs_user_id');
            $fs_users = FsUser::whereIn('id', $ids)->whereNotNull('last_login_time')->get(['id', 'last_login_time', 'last_charge_time'])->toArray();
            foreach ($fs_users as $fs_user) {
                foreach ($record_arr as $record) {
                    $time_arr = explode(',', $record->time);
                    array_multisort($time_arr, SORT_ASC); //对时间数组升序排序
                    if ($fs_user['id'] == $record->fs_user_id && $fs_user['last_login_time'] >= $time_arr[0]) {
                        $login_number++;
                    }
                    if ($fs_user['id'] == $record->fs_user_id && $fs_user['last_charge_time'] >= $time_arr[0]) {
                        $charge_number++;
                    }
                }
            }
        }

        $number['login_number'] = $login_number;
        $number['charge_number'] = $charge_number;
        return $number;
    }

    public function countData($counts, $record_times, $ids_diff, $type)
    {
        $now = Carbon::now()->toDateTimeString();
        if (empty($record_times) || empty($ids_diff)) {
            $no_pass = [];
            foreach ($counts->toArray() as $key => $count) {
                $no_pass[$key]['call_times'] = $count->call_times ?? 0;
                $no_pass[$key]['visit_number'] = $count->visit_number ?? 0;
                $no_pass[$key]['connect_number'] = $count->branch_connect_number ?? 0;
                $no_pass[$key]['login_number'] = 0;
                $no_pass[$key]['charge_number'] = 0;
                $no_pass[$key]['type'] = $type;
                $no_pass[$key]['any_id'] = $type == 3 ? $count->branch_id : $count->section_id;
                $no_pass[$key]['created_at'] = $now;
                $no_pass[$key]['updated_at'] = $now;
            }

            return $no_pass;
        }

        //统计回访后登录的人数和充值的人数
        $fs_users = FsUser::whereIn('id', $ids_diff)->whereNotNull('last_login_time')->get(['id', 'last_login_time', 'last_charge_time'])->toArray();

        $data_arr = [];
        foreach ($counts->toArray() as $key => $count) {
            $data_arr[$key]['call_times'] = $count->call_times;
            $data_arr[$key]['visit_number'] = $count->visit_number;
            $data_arr[$key]['connect_number'] = $type == 3 ? $count->branch_connect_number : $count->section_connect_number;
            $data_arr[$key]['login_number'] = 0;
            $data_arr[$key]['charge_number'] = 0;
            foreach ($record_times as $record_time) {
                if (in_array($record_time->fs_user_id, $count->ids)) {
                    if ($fs_users) {
                        foreach ($fs_users as $fs_user) {
                            if ($fs_user['id'] == $record_time->fs_user_id && $fs_user['last_login_time'] >= $record_time->created_at) {
                                $data_arr[$key]['login_number']++;
                            }
                            if ($fs_user['id'] == $record_time->fs_user_id && $fs_user['last_charge_time'] >= $record_time->created_at) {
                                $data_arr[$key]['charge_number']++;
                            }
                        }
                    }
                }
            }
            $data_arr[$key]['type'] = $type;
            $data_arr[$key]['any_id'] = $type == 3 ? $count->branch_id : $count->section_id;
            $data_arr[$key]['created_at'] = $now;
            $data_arr[$key]['updated_at'] = $now;
        }

        return $data_arr;
    }

    /*
     * 统计单个客服
     *
     * 返回分页信息
     */
    public function getCustomVisit(array $params = [])
    {
        DB::statement("set @@sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'");//取消严格模式，严格模式不能使用groupBy

        if (Carbon::now()->toTimeString() < '08:05:00') {
            //今天0点
            $today = Carbon::today();

            //前一天日期
            $past_day = Carbon::yesterday();

            //近七天日期
            $recent_week = Carbon::today()->subDays(7);

            //近一个月日期
            $recent_month = Carbon::today()->modify('-1 months');
        } else {
            //今天24点
            $today = Carbon::tomorrow();

            //今天日期
            $past_day = Carbon::today();

            //近七天日期
            $recent_week = Carbon::today()->subDays(6);

            //近一个月日期
            $recent_month = Carbon::today()->modify('-1 months')->addDays(1);
        }

        $record_data = [];

        //time_type 时间类型 1、前一天 2、近一周 3、近一个月
        if ($params['time_type'] = 1) {
            $record_data = DB::table($this->table)
                ->select('any_id', 'type', 'users.name', DB::raw('sum(call_times) as call_times, sum(visit_number) as visit_number, sum(connect_number) as connect_number, sum(login_number) as login_number, sum(charge_number) as charge_number, sum(member_number) as member_number'))
                ->rightJoin('users', 'users.id', '=', 'statistic_phone_records.any_id')
                ->where('type', 0)
                ->whereBetween('statistic_phone_records.created_at', [$past_day, $today])
                ->groupBy('any_id')
                ->paginate($params['limit'] ?? 10);
        }

        if ($params['time_type'] = 2) {
            $record_data = DB::table($this->table)
                ->select('any_id', 'type', 'users.name', DB::raw('sum(call_times) as call_times, sum(visit_number) as visit_number, sum(connect_number) as connect_number, sum(login_number) as login_number, sum(charge_number) as charge_number, sum(member_number) as member_number'))
                ->rightJoin('users', 'users.id', '=', 'statistic_phone_records.any_id')
                ->where('type', 0)
                ->whereBetween('statistic_phone_records.created_at', [$recent_week, $today])
                ->groupBy('any_id')
                ->paginate($params['limit'] ?? 10);
        }

        if ($params['time_type'] = 3) {
            $record_data = DB::table($this->table)
                ->select('any_id', 'type', 'users.name', DB::raw('sum(call_times) as call_times, sum(visit_number) as visit_number, sum(connect_number) as connect_number, sum(login_number) as login_number, sum(charge_number) as charge_number, sum(member_number) as member_number'))
                ->rightJoin('users', 'users.id', '=', 'statistic_phone_records.any_id')
                ->where('type', 0)
                ->whereBetween('statistic_phone_records.created_at', [$recent_month, $today])
                ->groupBy('any_id')
                ->paginate($params['limit'] ?? 10);
        }

        $result = [];
        if (!count($record_data)) {
            return $result;
        }

        $data_arr = $record_data->toArray();
        foreach ($data_arr['data'] as $index => $value) {
            $data_arr['data'][$index]->call_times = (int)$value->call_times;
            $data_arr['data'][$index]->visit_number = (int)$value->visit_number;
            $data_arr['data'][$index]->connect_number = (int)$value->connect_number;
            $data_arr['data'][$index]->login_number = (int)$value->login_number;
            $data_arr['data'][$index]->charge_number = (int)$value->charge_number;
            $data_arr['data'][$index]->member_number = (int)$value->member_number;
        }

        $result['data'] = $data_arr['data'];
        $result['meta']['pagination']['total'] = (int)$data_arr['total'];
        $result['meta']['pagination']['count'] = count($record_data);
        $result['meta']['pagination']['per_page'] = (int)$data_arr['per_page'];
        $result['meta']['pagination']['current_page'] = (int)$data_arr['current_page'];
        $result['meta']['pagination']['total_pages'] = (int)$data_arr['last_page'];
        if (count($record_data) <= ($params['limit'] ?? 0)) {
            $result['meta']['pagination']['links'] = null;
        } else {
            $result['meta']['pagination']['links']['previous'] = $data_arr['prev_page_url'];
            $result['meta']['pagination']['links']['next'] = $data_arr['next_page_url'];
        }

        return $result;
    }

    /*
     * 统计部门客服
     *
     * 返回所有部门统计及其降序排序部门数据
     */
    public function getSectionVisit(array $params = [])
    {
        if (Carbon::now()->toTimeString() < '08:05:00') {
            //今天0点
            $today = Carbon::today();

            //近三天日期
            $past_day = Carbon::today()->subDays(3);

            //近七天日期
            $recent_week = Carbon::today()->subDays(7);

            //近一个月日期
            $recent_month = Carbon::today()->modify('-1 months');
        } else {
            //今天24点
            $today = Carbon::tomorrow();

            //近三天日期
            $past_day = Carbon::today()->subDays(2);

            //近七天日期
            $recent_week = Carbon::today()->subDays(6);

            //近一个月日期
            $recent_month = Carbon::today()->modify('-1 months')->addDays(1);
        }

        return $this->getRecordDataByTab($params, $today, $past_day, $recent_week, $recent_month);
    }

    public function getRecordDataByTab($params, $today, $past_day, $recent_week, $recent_month)
    {
        DB::statement("set @@sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'");//取消严格模式，严格模式不能使用groupBy

        $record_data = [];

        //time_type 时间类型 1、近3天 2、近7天 3、近一个月
        //value_type 统计类型1、回访次数 2、回访人数 3、回访登录人数 4、回访充值人数
        if ($params['time_type'] == 1) {
            if ($params['value_type'] == 1) {
                $record_data = $this->getDayCallTimes($past_day, $today);
            }

            if ($params['value_type'] == 2) {
                $record_data = $this->getDayVisitNumber($past_day, $today);
            }

            if ($params['value_type'] == 3) {
                $record_data = $this->getDayLoginNumber($past_day, $today);
            }

            if ($params['value_type'] == 4) {
                $record_data = $this->getDayChargeNumber($past_day, $today);
            }
        }

        if ($params['time_type'] == 2) {
            if ($params['value_type'] == 1) {
                $record_data = $this->getWeekCallTimes($recent_week, $today);
            }
            if ($params['value_type'] == 2) {
                $record_data = $this->getWeekVisitNumber($recent_week, $today);
            }
            if ($params['value_type'] == 3) {
                $record_data = $this->getWeekLoginNumber($recent_week, $today);
            }
            if ($params['value_type'] == 4) {
                $record_data = $this->getWeekChargeNumber($recent_week, $today);
            }
        }

        if ($params['time_type'] == 3) {
            if ($params['value_type'] == 1) {
                $record_data = $this->getMonthCallTimes($recent_month, $today);
            }
            if ($params['value_type'] == 2) {
                $record_data = $this->getMonthVisitNumber($recent_month, $today);
            }
            if ($params['value_type'] == 3) {
                $record_data = $this->getMonthLoginNumber($recent_month, $today);
            }
            if ($params['value_type'] == 4) {
                $record_data = $this->getMonthChargeNumber($recent_month, $today);
            }
        }

        if (count($record_data)) {
            foreach ($record_data as $index => $value) {
                $record_data[$index]->value = (int)$value->value;
            }
        }

        return $record_data;
    }

    public function getMonthCallTimes($recent_month, $today)
    {
        return DB::table($this->table)
            ->select('any_id', 'type', 'roles.name', DB::raw('sum(call_times) as value'))
            ->rightJoin('roles', 'roles.id', '=', 'statistic_phone_records.any_id')
            ->where('type', 2)
            ->whereBetween('statistic_phone_records.created_at', [$recent_month, $today])
            ->groupBy('any_id')
            ->orderBy('call_times', 'DESC')
            ->get();
    }

    public function getMonthVisitNumber($recent_month, $today)
    {
        return DB::table($this->table)
            ->select('any_id', 'type', 'roles.name', DB::raw('sum(visit_number) as value'))
            ->rightJoin('roles', 'roles.id', '=', 'statistic_phone_records.any_id')
            ->where('type', 2)
            ->whereBetween('statistic_phone_records.created_at', [$recent_month, $today])
            ->groupBy('any_id')
            ->orderBy('visit_number', 'DESC')
            ->get();
    }

    public function getMonthLoginNumber($recent_month, $today)
    {
        return DB::table($this->table)
            ->select('any_id', 'type', 'roles.name', DB::raw('sum(login_number) as value'))
            ->rightJoin('roles', 'roles.id', '=', 'statistic_phone_records.any_id')
            ->where('type', 2)
            ->whereBetween('statistic_phone_records.created_at', [$recent_month, $today])
            ->groupBy('any_id')
            ->orderBy('login_number', 'DESC')
            ->get();
    }

    public function getMonthChargeNumber($recent_month, $today)
    {
        return DB::table($this->table)
            ->select('any_id', 'type', 'roles.name', DB::raw('sum(charge_number) as value'))
            ->rightJoin('roles', 'roles.id', '=', 'statistic_phone_records.any_id')
            ->where('type', 2)
            ->whereBetween('statistic_phone_records.created_at', [$recent_month, $today])
            ->groupBy('any_id')
            ->orderBy('charge_number', 'DESC')
            ->get();
    }

    public function getWeekCallTimes($recent_week, $today)
    {
        return DB::table($this->table)
            ->select('any_id', 'type', 'roles.name', DB::raw('sum(call_times) as value'))
            ->rightJoin('roles', 'roles.id', '=', 'statistic_phone_records.any_id')
            ->where('type', 2)
            ->whereBetween('statistic_phone_records.created_at', [$recent_week, $today])
            ->groupBy('any_id')
            ->orderBy('call_times', 'DESC')
            ->get();
    }

    public function getWeekVisitNumber($recent_week, $today)
    {
        return DB::table($this->table)
            ->select('any_id', 'type', 'roles.name', DB::raw('sum(visit_number) as value'))
            ->rightJoin('roles', 'roles.id', '=', 'statistic_phone_records.any_id')
            ->where('type', 2)
            ->whereBetween('statistic_phone_records.created_at', [$recent_week, $today])
            ->groupBy('any_id')
            ->orderBy('visit_number', 'DESC')
            ->get();
    }

    public function getWeekLoginNumber($recent_week, $today)
    {
        return DB::table($this->table)
            ->select('any_id', 'type', 'roles.name', DB::raw('sum(login_number) as value'))
            ->rightJoin('roles', 'roles.id', '=', 'statistic_phone_records.any_id')
            ->where('type', 2)
            ->whereBetween('statistic_phone_records.created_at', [$recent_week, $today])
            ->groupBy('any_id')
            ->orderBy('login_number', 'DESC')
            ->get();
    }

    public function getWeekChargeNumber($recent_week, $today)
    {
        return DB::table($this->table)
            ->select('any_id', 'type', 'roles.name', DB::raw('sum(charge_number) as value'))
            ->rightJoin('roles', 'roles.id', '=', 'statistic_phone_records.any_id')
            ->where('type', 2)
            ->whereBetween('statistic_phone_records.created_at', [$recent_week, $today])
            ->groupBy('any_id')
            ->orderBy('charge_number', 'DESC')
            ->get();
    }

    public function getDayCallTimes($past_day, $today)
    {
        return DB::table($this->table)
            ->select('any_id', 'type', 'roles.name', DB::raw('sum(call_times) as value'))
            ->rightJoin('roles', 'roles.id', '=', 'statistic_phone_records.any_id')
            ->where('type', 2)
            ->whereBetween('statistic_phone_records.created_at', [$past_day, $today])
            ->groupBy('any_id')
            ->orderBy('call_times', 'DESC')
            ->get();
    }

    public function getDayVisitNumber($past_day, $today)
    {
        return DB::table($this->table)
            ->select('any_id', 'type', 'roles.name', DB::raw('sum(visit_number) as value'))
            ->rightJoin('roles', 'roles.id', '=', 'statistic_phone_records.any_id')
            ->where('type', 2)
            ->whereBetween('statistic_phone_records.created_at', [$past_day, $today])
            ->groupBy('any_id')
            ->orderBy('visit_number', 'DESC')
            ->get();
    }

    public function getDayLoginNumber($past_day, $today)
    {
        return DB::table($this->table)
            ->select('any_id', 'type', 'roles.name', DB::raw('sum(login_number) as value'))
            ->rightJoin('roles', 'roles.id', '=', 'statistic_phone_records.any_id')
            ->where('type', 2)
            ->whereBetween('statistic_phone_records.created_at', [$past_day, $today])
            ->groupBy('any_id')
            ->orderBy('login_number', 'DESC')
            ->get();
    }

    public function getDayChargeNumber($past_day, $today)
    {
        return DB::table($this->table)
            ->select('any_id', 'type', 'roles.name', DB::raw('sum(charge_number) as value'))
            ->rightJoin('roles', 'roles.id', '=', 'statistic_phone_records.any_id')
            ->where('type', 2)
            ->whereBetween('statistic_phone_records.created_at', [$past_day, $today])
            ->groupBy('any_id')
            ->orderBy('charge_number', 'DESC')
            ->get();
    }

    public function getGrade(array $params = [])
    {
        //本周
        $weekdate = weekTime();

        //本月
        $month = getMonth();

        //统计每周的第一天需要加一天，因为每天的统计都是前一天的数据
        $week_start_one = date('Y-m-d H:i:s', strtotime("+1 day", strtotime($weekdate['week_start'])));

        $month_start_one = date('Y-m-d H:i:s', strtotime("+1 day", strtotime($month['month_start'])));

        $user_id = Auth::User()->id;

        $result['task'] = [];

        $result['phone'] = [];

        $result['sms'] = [];

        if ($params['time_type'] == 1) {
            $start = $weekdate['week_start'];
            $end = $weekdate['week_end'];
            $start_one = $week_start_one;
        }

        if ($params['time_type'] == 2) {
            $start = $month['month_start'];
            $end = $month['month_end'];
            $start_one = $month_start_one;
        }

        if ($params['time_type'] == 3) {
            $start = '';
            $end = '';
            $start_one = '';
        }

        return $this->getDataByTypeParams($user_id, $start, $end, $start_one, $result, $params);
    }

    public function getDataByTypeParams($user_id, $start, $end, $start_one, $result, $params)
    {
        //type为1：个人绩效 2:整体绩效
        //time_type 时间类型 1、本周 2、本月 3、全部
        if ($params['type'] == 1) {
            //任务
            $task_number = $this->persionalTasks($user_id, $start, $end, $params['time_type'], $params['type']);

            $finish_number = $this->persionalCount($user_id, $start, $end, 1, $params['time_type'], $params['type']);

            // $over_number = $this->persionalCount($user_id, $start, $end, 2, $params['time_type'], $params['type']);

            $over_not_number = $this->persionalCount($user_id, $start, $end, 3, $params['time_type'], $params['type']);

            $over_yes_number = $this->persionalCount($user_id, $start, $end, 4, $params['time_type'], $params['type']);

            //电话
            $call_times = $this->persionalNumber($user_id, $start_one, $end, 'sum(call_times) as call_times', $params['time_type'], 'statistic_phone_records', $params['type']);

            $visit_number = $this->persionalNumber($user_id, $start_one, $end, 'sum(visit_number) as visit_number', $params['time_type'], 'statistic_phone_records', $params['type']);

            $connect_number = $this->persionalNumber($user_id, $start_one, $end, 'sum(connect_number) as connect_number', $params['time_type'], 'statistic_phone_records', $params['type']);

            $login_number = $this->persionalNumber($user_id, $start_one, $end, 'sum(login_number) as login_number', $params['time_type'], 'statistic_phone_records', $params['type']);

            //短信
            $charge_number = $this->persionalNumber($user_id, $start_one, $end, 'sum(charge_number) as charge_number', $params['time_type'], 'statistic_phone_records', $params['type']);
            $sms_count = $this->persionalNumber($user_id, $start_one, $end, 'sum(sms_count) as sms_count', $params['time_type'], 'statistic_sms_records', $params['type']);
            $sms_number = $this->persionalNumber($user_id, $start_one, $end, 'sum(sms_number) as sms_number', $params['time_type'], 'statistic_sms_records', $params['type']);

            $result['task']['task_count'] = count($task_number) ? (int)$task_number[0]->task_number : 0;
            $result['task']['finish_count'] = count($finish_number);
            $result['task']['over_number'] = count($over_not_number) + count($over_yes_number);
            $result['task']['over_not_number'] = count($over_not_number);
            $result['task']['over_yes_number'] = count($over_yes_number);
            $result['phone']['call_times'] = count($call_times) ? (int)$call_times[0]->call_times : 0;
            $result['phone']['visit_number'] = count($visit_number) ? (int)$visit_number[0]->visit_number : 0;
            $result['phone']['login_number'] = count($login_number) ? (int)$login_number[0]->login_number : 0;
            $result['sms']['charge_number'] = count($charge_number) ? (int)$charge_number[0]->charge_number : 0;
            $result['sms']['sms_count'] = count($sms_count) ? (int)$sms_count[0]->sms_count : 0;
            $result['sms']['sms_number'] = count($sms_number) ? (int)$sms_number[0]->sms_number : 0;

            if ($result['task']['task_count'] > 0) {
                $result['task']['finish_rate'] = round((count($finish_number) + count($over_yes_number)) / $result['task']['task_count'], 2);
            } else {
                $result['task']['finish_rate'] = 0;
            }

            if (count($visit_number) && ((int)$visit_number[0]->visit_number > 0)) {
                $result['phone']['connect_rate'] = round((int)$connect_number[0]->connect_number / (int)$visit_number[0]->visit_number, 2);
                $result['phone']['login_rate'] = round((int)$login_number[0]->login_number / (int)$visit_number[0]->visit_number, 2);
                $result['sms']['charge_rate'] = round((int)$charge_number[0]->charge_number / (int)$visit_number[0]->visit_number, 2);
            } else {
                $result['phone']['login_rate'] = 0;
                $result['phone']['connect_rate'] = 0;
                $result['sms']['charge_rate'] = 0;
            }
        }

        //整体绩效
        if ($params['type'] == 2) {
            //任务
            $task_number = $this->persionalTasks($user_id, $start, $end, $params['time_type'], $params['type']);

            $finish_number = $this->persionalCount($user_id, $start, $end, 1, $params['time_type'], $params['type']);

            // $over_number = $this->persionalCount($user_id, $start, $end, 2, $params['time_type'], $params['type']);

            $over_not_number = $this->persionalCount($user_id, $start, $end, 3, $params['time_type'], $params['type']);

            $over_yes_number = $this->persionalCount($user_id, $start, $end, 4, $params['time_type'], $params['type']);

            //电话
            $call_times = $this->persionalNumber($user_id, $start_one, $end, 'sum(call_times) as call_times', $params['time_type'], 'statistic_phone_records', $params['type']);

            $visit_number = $this->persionalNumber($user_id, $start_one, $end, 'sum(visit_number) as visit_number', $params['time_type'], 'statistic_phone_records', $params['type']);

            $connect_number = $this->persionalNumber($user_id, $start_one, $end, 'sum(connect_number) as connect_number', $params['time_type'], 'statistic_phone_records', $params['type']);

            $login_number = $this->persionalNumber($user_id, $start_one, $end, 'sum(login_number) as login_number', $params['time_type'], 'statistic_phone_records', $params['type']);

            //短信
            $charge_number = $this->persionalNumber($user_id, $start_one, $end, 'sum(charge_number) as charge_number', $params['time_type'], 'statistic_phone_records', $params['type']);

            $sms_count = $this->persionalNumber($user_id, $start_one, $end, 'sum(sms_count) as sms_count', $params['time_type'], 'statistic_sms_records', $params['type']);

            $sms_number = $this->persionalNumber($user_id, $start_one, $end, 'sum(sms_number) as sms_number', $params['time_type'], 'statistic_sms_records', $params['type']);

            $result['task']['task_count'] = count($task_number) ? (int)$task_number[0]->task_number : 0;
            $result['task']['finish_count'] = count($finish_number);
            $result['task']['over_number'] = count($over_not_number) + count($over_yes_number);
            $result['task']['over_not_number'] = count($over_not_number);
            $result['task']['over_yes_number'] = count($over_yes_number);
            $result['phone']['call_times'] = count($call_times) ? (int)$call_times[0]->call_times : 0;
            $result['phone']['visit_number'] = count($visit_number) ? (int)$visit_number[0]->visit_number : 0;
            $result['phone']['login_number'] = count($login_number) ? (int)$login_number[0]->login_number : 0;
            $result['sms']['charge_number'] = count($charge_number) ? (int)$charge_number[0]->charge_number : 0;
            $result['sms']['sms_count'] = count($sms_count) ? (int)$sms_count[0]->sms_count : 0;
            $result['sms']['sms_number'] = count($sms_number) ? (int)$sms_number[0]->sms_number : 0;

            if ($result['task']['task_count'] > 0) {
                $result['task']['finish_rate'] = round((count($finish_number) + count($over_yes_number)) / $result['task']['task_count'], 2);
            } else {
                $result['task']['finish_rate'] = 0;
            }

            if (count($visit_number) && ((int)$visit_number[0]->visit_number > 0)) {
                $result['phone']['connect_rate'] = round((int)$connect_number[0]->connect_number / (int)$visit_number[0]->visit_number, 2);
                $result['phone']['login_rate'] = round((int)$login_number[0]->login_number / (int)$visit_number[0]->visit_number, 2);
                $result['sms']['charge_rate'] = round((int)$charge_number[0]->charge_number / (int)$visit_number[0]->visit_number, 2);
            } else {
                $result['phone']['login_rate'] = 0;
                $result['phone']['connect_rate'] = 0;
                $result['sms']['charge_rate'] = 0;
            }
        }

        return $result;
    }

    public function persionalTasks($user_id, $start, $end, $time_type, $type)
    {
        //type为1：个人绩效 2:整体绩效
        //time_type 时间类型 1、本周 2、本月 3、全部
        if ($type == 1) {
            if ($time_type == 3) {
                return DB::table('tasks')
                    ->select(DB::raw('count(id) as task_number'))
                    ->where('carry_user_id', $user_id)
                    ->get();
            }

            return DB::table('tasks')
                ->select(DB::raw('count(id) as task_number'))
                ->where('carry_user_id', $user_id)
                ->whereBetween('start_time', [$start, $end])
                ->get();
        }

        if ($type == 2) {
            if ($time_type == 3) {
                return DB::table('tasks')
                    ->select(DB::raw('count(id) as task_number'))
                    ->get();
            }

            return DB::table('tasks')
                ->select(DB::raw('count(id) as task_number'))
                ->whereBetween('start_time', [$start, $end])
                ->get();
        }

        return [];
    }

    public function persionalCount($user_id, $start, $end, $status, $time_type, $type)
    {
        //type为1：个人绩效 2:整体绩效
        //time_type 时间类型 1、本周 2、本月 3、全部
        if ($type == 1) {
            if ($time_type == 3) {
                return DB::table('tasks')
                    ->select('carry_user_id', 'status')
                    ->where('carry_user_id', $user_id)
                    ->where('status', $status)
                    ->get();
            }

            return DB::table('tasks')
                ->select('carry_user_id', 'status')
                ->where('carry_user_id', $user_id)
                ->where('status', $status)
                ->whereBetween('start_time', [$start, $end])
                ->get();
        }

        if ($type == 2) {
            if ($time_type == 3) {
                return DB::table('tasks')
                    ->select('carry_user_id', 'status')
                    ->where('status', $status)
                    ->get();
            }

            return DB::table('tasks')
                ->select('carry_user_id', 'status')
                ->where('status', $status)
                ->whereBetween('start_time', [$start, $end])
                ->get();
        }

        return [];
    }

    public function persionalNumber($user_id, $start, $end, $sql, $time_type, $table, $type)
    {
        //type为1：个人绩效 2:整体绩效
        //time_type 时间类型 1、本周 2、本月 3、全部
        if ($type == 1) {
            if ($time_type == 3) {
                return DB::table($table)
                    ->select(DB::raw($sql))
                    ->where('any_id', $user_id)
                    ->where('type', 0)
                    ->get();
            }

            return DB::table($table)
                ->select(DB::raw($sql))
                ->where('any_id', $user_id)
                ->where('type', 0)
                ->whereBetween('created_at', [$start, $end])
                ->get();
        }

        if ($type == 2) {
            if ($time_type == 3) {
                return DB::table($table)
                    ->select(DB::raw($sql))
                    ->where('type', 1)
                    ->get();
            }

            return DB::table($table)
                ->select(DB::raw($sql))
                ->where('type', 1)
                ->whereBetween('created_at', [$start, $end])
                ->get();
        }

        return [];
    }

    public function getRecentTasks()
    {
        $user_id = Auth::User()->id;

        //今天0点
        $today = Carbon::today();

        //第一天0点
        $one_day = Carbon::today()->subDays(7);

        //第二天0点
        $two_day = Carbon::today()->subDays(6);

        //第三天0点
        $three_day = Carbon::today()->subDays(5);

        //第四天0点
        $four_day = Carbon::today()->subDays(4);

        //第五天0点
        $five_day = Carbon::today()->subDays(3);

        //第六天0点
        $six_day = Carbon::today()->subDays(2);

        //第七天0点
        $seven_day = Carbon::today()->subDays(1);

        //第一天是几号
        $one_number = date('d', Carbon::today()->subDays(7)->timestamp);

        //第二天是几号
        $two_number = date('d', Carbon::today()->subDays(6)->timestamp);

        //第三天是几号
        $three_number = date('d', Carbon::today()->subDays(5)->timestamp);

        //第四天是几号
        $four_number = date('d', Carbon::today()->subDays(4)->timestamp);

        //第五天是几号
        $five_number = date('d', Carbon::today()->subDays(3)->timestamp);

        //第六天是几号
        $six_number = date('d', Carbon::today()->subDays(2)->timestamp);

        //第七天是几号
        $seven_number = date('d', Carbon::today()->subDays(1)->timestamp);

        $first_day_sql = "(select count(*) from phone_records where user_id = " . $user_id . " and created_at between '" . $one_day . "' and '" . $two_day . "') as '" . $one_number . "',";
        $second_day_sql = "(select count(*) from phone_records where user_id = " . $user_id . " and created_at between '" . $two_day . "' and '" . $three_day . "') as '" . $two_number . "',";
        $third_day_sql = "(select count(*) from phone_records where user_id = " . $user_id . " and created_at between '" . $three_day . "' and '" . $four_day . "') as '" . $three_number . "',";
        $fourth_day_sql = "(select count(*) from phone_records where user_id = " . $user_id . " and created_at between '" . $four_day . "' and '" . $five_day . "') as '" . $four_number . "',";
        $fifth_day_sql = "(select count(*) from phone_records where user_id = " . $user_id . " and created_at between '" . $five_day . "' and '" . $six_day . "') as '" . $five_number . "',";
        $sixth_day_sql = "(select count(*) from phone_records where user_id = " . $user_id . " and created_at between '" . $six_day . "' and '" . $seven_day . "') as '" . $six_number . "',";
        $seventh_day_sql = "(select count(*) from phone_records where user_id = " . $user_id . " and created_at between '" . $seven_day . "' and '" . $today . "') as '" . $seven_number . "'";

        $all_sql = $first_day_sql . $second_day_sql . $third_day_sql . $fourth_day_sql . $fifth_day_sql . $sixth_day_sql . $seventh_day_sql;
        $records = DB::table('phone_records')
            ->select(DB::raw($all_sql))
            ->where('user_id', $user_id)
            ->groupBy('user_id')
            ->get();

        if (count($records)) {
            return $records[0];
        }

        return [];
    }
}
